#!/usr/bin/python
# -*- coding: UTF-8 -*-
import pandas as pd
import openpyxl
import re

# 定义用于处理的手机号字段名称
phone_column = '手机号'

def read_all_excel_sheets(filename):
    xls = pd.ExcelFile(filename)
    all_dfs = [xls.parse(sheet, header=1, dtype=str) for sheet in xls.sheet_names]
    return pd.concat(all_dfs)


# 读取三个 Excel 文件的所有工作表数据，并进行合并
data = pd.concat([read_all_excel_sheets('phone1.xlsx'),
                  read_all_excel_sheets('phone2.xlsx'),
                  read_all_excel_sheets('phone3.xlsx')])
data = data.dropna(subset=[phone_column])

# 处理手机号
def process_phone_number(phone):
    phone = str(phone)
    mainland_pattern = re.compile(r'^1\d{10}$')
    hk_mo_tw_pattern = re.compile(r'^(\+?(852|853|886)\d{8,13}|(852|853|886)\d{8,13})$')
    if not mainland_pattern.match(phone) and not hk_mo_tw_pattern.match(phone):
        print('非法手机号：' + phone)

    # 去除空格
    phone = phone.replace(' ', '')
    phone = phone.replace('‘', '')
    phone = phone.replace('\'', '')
    phone = phone.replace('+86', '')
    if phone.startswith('853') or phone.startswith('852'):
        phone = '+' + phone
    return phone


data[phone_column] = data[phone_column].apply(process_phone_number)

data['份数'] = data.groupby(phone_column)[phone_column].transform('count')

data.drop_duplicates(subset=phone_column, keep='first', inplace=True)

excel = data[[phone_column, '份数']]
df_sorted = excel.sort_values(by='份数')

# 读取模板文件
wb = openpyxl.load_workbook('模板.xlsx')
sheet = wb.active

# 填充数据到模板中
row_number = 2  # 开始填充数据的行数
for index, row in df_sorted.iterrows():
    sheet[f'A{row_number}'] = str(row[phone_column])
    sheet[f'B{row_number}'] = row['份数']
    row_number += 1

# 保存填充数据后的 Excel 文件
wb.save('自行车.xlsx')
